package u.can.i.up.office;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class XlsxReader {

	public volatile static XlsxReader mXlsxReader = new XlsxReader();

	@SuppressWarnings("unused")
	private Logger logger = LogManager.getLogger(XlsxReader.class);

	public XlsxReader() {

	}

	public static XlsxReader getInstance() {
		return mXlsxReader;
	}

	public Map<String, AppMetrics> readMapFromAppDataSet(String xlsxPath, int sheetAt) {
		Map<String, AppMetrics> hashMap = new HashMap<String, AppMetrics>();
		try {
			InputStream inp = new FileInputStream(xlsxPath);
			Workbook wb = WorkbookFactory.create(inp);
			Sheet sheet = wb.getSheetAt(sheetAt);
			if (sheet != null) {
				for (int i = 1; i <= sheet.getLastRowNum(); i++) {
					Row currentRow = sheet.getRow(i);
					int ID = (int) currentRow.getCell(0).getNumericCellValue();
					String packagename = currentRow.getCell(1).getStringCellValue();
					double crspr = currentRow.getCell(2).getNumericCellValue();
					double crsprp = currentRow.getCell(3).getNumericCellValue();
					double pagerank = currentRow.getCell(4).getNumericCellValue();
					AppMetrics am = new AppMetrics(ID, packagename, crspr, crsprp, pagerank);
					hashMap.put(packagename, am);
				}
			}
		} catch (EncryptedDocumentException | IOException | InvalidFormatException e) {
			e.printStackTrace();
		}
		return hashMap;
	}

	public List<AppMetrics> readListFromAppDataSet(String xlsxPath, int sheetAt) {
		List<AppMetrics> list = new ArrayList<AppMetrics>();
		try {
			InputStream inp = new FileInputStream(xlsxPath);
			Workbook wb = WorkbookFactory.create(inp);
			Sheet sheet = wb.getSheetAt(sheetAt);
			if (sheet != null) {
				for (int i = 1; i <= sheet.getLastRowNum(); i++) {
					Row currentRow = sheet.getRow(i);
					int ID = (int) currentRow.getCell(0).getNumericCellValue();
					String packagename = currentRow.getCell(1).getStringCellValue();
					double crspr = currentRow.getCell(2).getNumericCellValue();
					double crsprp = currentRow.getCell(3).getNumericCellValue();
					double pagerank = currentRow.getCell(4).getNumericCellValue();
					AppMetrics am = new AppMetrics(ID, packagename, crspr, crsprp, pagerank);
					list.add(am);
				}
			}
		} catch (EncryptedDocumentException | IOException | InvalidFormatException e) {
			e.printStackTrace();
		}
		return list;
	}

	public Map<String, Map<String, AppMetrics>> readMapFromComponentDataSet(String xlsxPath, int sheetAt) {
		Map<String, Map<String, AppMetrics>> hashMap = new HashMap<String, Map<String, AppMetrics>>();
		try {
			InputStream inp = new FileInputStream(xlsxPath);
			Workbook wb = WorkbookFactory.create(inp);
			Sheet sheet = wb.getSheetAt(sheetAt);
			if (sheet != null) {
				for (int i = 1; i <= sheet.getLastRowNum(); i++) {
					Row currentRow = sheet.getRow(i);
					int ID = (int) currentRow.getCell(0).getNumericCellValue();
					String componentname = currentRow.getCell(1).getStringCellValue();
					String packagename = currentRow.getCell(2).getStringCellValue();
					double crspr = currentRow.getCell(3).getNumericCellValue();
					double crsprp = currentRow.getCell(4).getNumericCellValue();
					double pagerank = currentRow.getCell(5).getNumericCellValue();
					AppMetrics am = new AppMetrics(ID, packagename, componentname, crspr, crsprp, pagerank);
					if (hashMap.get(packagename) != null && !hashMap.get(packagename).isEmpty()) {
						hashMap.get(packagename).put(componentname, am);
					} else {
						Map<String, AppMetrics> tmp = new HashMap<String, AppMetrics>();
						tmp.put(componentname, am);
						hashMap.put(packagename, tmp);
					}
				}
			}
		} catch (EncryptedDocumentException | IOException | InvalidFormatException e) {
			e.printStackTrace();
		}
		return hashMap;
	}

	public Map<String, List<AppMetrics>> readListFromComponentDataSet(String xlsxPath, int sheetAt) {
		Map<String, List<AppMetrics>> hashMap = new HashMap<String, List<AppMetrics>>();
		try {
			InputStream inp = new FileInputStream(xlsxPath);
			Workbook wb = WorkbookFactory.create(inp);
			Sheet sheet = wb.getSheetAt(sheetAt);
			if (sheet != null) {
				for (int i = 1; i <= sheet.getLastRowNum(); i++) {
					Row currentRow = sheet.getRow(i);
					int ID = (int) currentRow.getCell(0).getNumericCellValue();
					String componentname = currentRow.getCell(1).getStringCellValue();
					String packagename = currentRow.getCell(2).getStringCellValue();
					double crspr = currentRow.getCell(3).getNumericCellValue();
					double crsprp = currentRow.getCell(4).getNumericCellValue();
					double pagerank = currentRow.getCell(5).getNumericCellValue();
					AppMetrics am = new AppMetrics(ID, packagename, componentname, crspr, crsprp, pagerank);
					if (hashMap.get(packagename) != null && !hashMap.get(packagename).isEmpty()) {
						hashMap.get(packagename).add(am);
					} else {
						List<AppMetrics> tmp = new ArrayList<AppMetrics>();
						tmp.add(am);
						hashMap.put(packagename, tmp);
					}
				}
			}
		} catch (EncryptedDocumentException | IOException | InvalidFormatException e) {
			e.printStackTrace();
		}
		return hashMap;
	}

	public Map<String, Double> readComponentCrsprDataSetLeThreshold(String xlsxPath, float threshold_percent) {
		Map<String, Double> hashMap = new HashMap<String, Double>();
		try {
			InputStream inp = new FileInputStream(xlsxPath);
			Workbook wb = WorkbookFactory.create(inp);
			Sheet sheet = wb.getSheetAt(0);
			if (sheet != null) {
				double threshold = sheet.getRow(1).getCell(1).getNumericCellValue() * threshold_percent;
				for (int i = 1; i <= sheet.getLastRowNum(); i++) {
					Row currentRow = sheet.getRow(i);
					String componentName = currentRow.getCell(0).getStringCellValue();
					double crsprp = currentRow.getCell(1).getNumericCellValue();
					if (crsprp < threshold) {
						break;
					}
					if (!hashMap.containsKey(componentName)) {
						hashMap.put(componentName, crsprp);
					}
				}
			}
		} catch (EncryptedDocumentException | IOException | InvalidFormatException e) {
			e.printStackTrace();
		}
		return hashMap;
	}

	public List<String> readFromFilter(String xlsxPath, int sheetAt) {
		List<String> list = new ArrayList<String>();
		Set<String> set = new HashSet<String>();
		try {
			InputStream inp = new FileInputStream(xlsxPath);
			Workbook wb = WorkbookFactory.create(inp);
			Sheet sheet = wb.getSheetAt(sheetAt);
			if (sheet != null) {
				for (int i = 1; i <= sheet.getLastRowNum(); i++) {
					Row currentRow = sheet.getRow(i);
					String packagename = currentRow.getCell(0).getStringCellValue();
					set.add(packagename);
				}
			}
		} catch (EncryptedDocumentException | IOException | InvalidFormatException e) {
			e.printStackTrace();
		}
		list.addAll(set);
		return list;
	}

	@SuppressWarnings({ "deprecation", "unused" })
	private String getTrueStringValue(Cell cell) {
		String value = "";
		if (null != cell) {
			switch (cell.getCellType()) {
			case HSSFCell.CELL_TYPE_NUMERIC: // 数字
				if (HSSFDateUtil.isCellDateFormatted(cell)) {
					// 如果是date类型则 ，获取该cell的date值
					value = HSSFDateUtil.getJavaDate(cell.getNumericCellValue()).toString();
				} else { // 纯数字
					value = String.valueOf(new DecimalFormat("#").format(cell.getNumericCellValue()));
				}
				break;
			case HSSFCell.CELL_TYPE_STRING: // 字符串
				value = "" + cell.getStringCellValue();
				break;
			case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
				value = "" + cell.getBooleanCellValue();
				break;
			case HSSFCell.CELL_TYPE_FORMULA: // 公式
				value = "" + cell.getCellFormula();
				break;
			case HSSFCell.CELL_TYPE_BLANK: // 空值
				value = "";
				break;
			case HSSFCell.CELL_TYPE_ERROR: // 故障
				value = "";
				break;
			default:
				value = "" + "未知类型";
				break;
			}
		} else {
			value = "" + "未知类型1";
		}
		return value;
	}

	@SuppressWarnings({ "deprecation", "unused" })
	private String getTrueStringValueMoney(Cell cell) {
		String value = "";
		if (null != cell) {
			switch (cell.getCellType()) {
			case HSSFCell.CELL_TYPE_NUMERIC: // 数字
				if (HSSFDateUtil.isCellDateFormatted(cell)) {
					// 如果是date类型则 ，获取该cell的date值
					value = HSSFDateUtil.getJavaDate(cell.getNumericCellValue()).toString();
				} else { // 纯数字
					value = String.valueOf(new DecimalFormat("¥ #,###.00").format(cell.getNumericCellValue()));
				}
				break;
			case HSSFCell.CELL_TYPE_STRING: // 字符串
				value = "" + cell.getStringCellValue();
				break;
			case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
				value = "" + cell.getBooleanCellValue();
				break;
			case HSSFCell.CELL_TYPE_FORMULA: // 公式
				value = "" + cell.getCellFormula();
				break;
			case HSSFCell.CELL_TYPE_BLANK: // 空值
				value = "";
				break;
			case HSSFCell.CELL_TYPE_ERROR: // 故障
				value = "";
				break;
			default:
				value = "" + "未知类型";
				break;
			}
		} else {
			value = "" + "未知类型1";
		}
		return value;
	}
}
